"""
Case Type   : CLEAN CONNECTION
Case Name   : 权限验证，三权分立参数打开，系统管理员用户 CLEAN CONNECTION 成功
Create At   : 2022-05-10
Owner       : liu-tong-8848
Description :
    1、设置参数并重启数据库enableSeparationOfDuty=on
    2、创建数据库
    3、创建用户
    4、线程1连接数据库
    5、线程2连接数据库
    6、线程3连接数据库
    7、线程4连接数据库
    8、线程5执行清理操作
    9、线程6执行清理操作
    10、线程7执行清理操作
    11、线程8执行清理操作
    12、清理环境
Expect      :
    1、设置参数并重启数据库，expect: 成功
    2、创建数据库，expect: 成功
    3、创建用户，expect: 成功
    4、线程1连接数据库，expect: 成功
    5、线程2连接数据库，expect: 成功
    6、线程3连接数据库，expect: 成功
    7、线程4连接数据库，expect: 成功
    8、线程5执行清理操作，expect: 失败
    9、线程6执行清理操作，expect: 成功
    10、线程7执行清理操作，expect: 成功
    11、线程8执行清理操作，expect: 成功
    12、清理环境，expect: 成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
import time
from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CleanConnection(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.db_name = 'd_clean_0031'
        self.u_name1 = 'u_clean_0031'
        self.u_name2 = 'u_clean_0031_1'
        self.u_name3 = 'u_clean_0031_2'
        self.u_name4 = 'u_clean_0031_3'
        self.flag = 'CLEAN CONNECTION'
        self.err_flag = 'connection to server was lost'
        self.default_value = self.common.show_param('enableSeparationOfDuty')

    def test_main(self):
        step_txt = '-----step1:设置参数并重启数据库enableSeparationOfDuty=on; ' \
               'expect:成功-----'
        self.log.info(step_txt)
        mod_msg = \
            self.pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          'enableSeparationOfDuty=on')
        self.log.info(mod_msg)
        self.assertTrue(mod_msg, '执行失败:' + step_txt)
        restart_msg = self.pri_sh.restart_db_cluster()
        self.log.info(restart_msg)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + step_txt)

        step_txt = '----step2: 创建数据库，expect: 成功----'
        self.log.info(step_txt)
        create_sql = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_DATABASE_SUCCESS) == 1,
            '执行失败:' + step_txt)

        step_txt = '----step3: 创建用户，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop user if exists {self.u_name1} cascade;" \
            f"create user {self.u_name1} sysadmin " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name2} cascade;" \
            f"create user {self.u_name2} createrole " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name3} cascade;" \
            f"create user {self.u_name3} auditadmin " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name4} cascade;" \
            f"create user {self.u_name4} " \
            f"password '{macro.COMMON_PASSWD}';"
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_ROLE_SUCCESS_MSG) == 4,
            '执行失败:' + step_txt)

        step4_txt = '----step4: 线程1连接数据库;expect:成功----'
        self.log.info(step4_txt)
        sql_cmd1 = 'select sysdate;select pg_sleep(31);select sysdate;'
        excute_cmd1 = ' '
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd1, excute_cmd1, self.db_name))
        session1.setDaemon(True)
        session1.start()

        step5_txt = '----step5: 线程2连接数据库;expect:成功----'
        self.log.info(step5_txt)
        sql_cmd2 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd2 = f'-U {self.u_name2} -W "{macro.COMMON_PASSWD}" '
        session2 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd2, excute_cmd2, self.db_name))
        session2.setDaemon(True)
        session2.start()

        step6_txt = '----step6: 线程3连接数据库;expect:成功----'
        self.log.info(step6_txt)
        sql_cmd3 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd3 = f'-U {self.u_name3} -W "{macro.COMMON_PASSWD}" '
        session3 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd3, excute_cmd3, self.db_name))
        session3.setDaemon(True)
        session3.start()

        step7_txt = '----step7: 线程4连接数据库;expect:成功----'
        self.log.info(step7_txt)
        sql_cmd4 = 'select sysdate;select pg_sleep(60);select sysdate;'
        excute_cmd4 = f'-U {self.u_name4} -W "{macro.COMMON_PASSWD}" '
        session4 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd4, excute_cmd4, self.db_name))
        session4.setDaemon(True)
        session4.start()
        time.sleep(5)

        step8_txt = '----step8: 线程5执行清理操作，expect: 清理失败----'
        self.log.info(step8_txt)
        sql_cmd5 = f'clean connection to all force ' \
                   f'to user {self.pri_root.ssh_user};'
        excute_cmd5 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session5 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd5, excute_cmd5, self.db_name))
        session5.setDaemon(True)
        session5.start()

        step9_txt = '----step9: 线程6执行清理操作，expect: 清理成功----'
        self.log.info(step9_txt)
        sql_cmd6 = f'clean connection to all force to user {self.u_name2};'
        excute_cmd6 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session6 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd6, excute_cmd6, self.db_name))
        session6.setDaemon(True)
        session6.start()

        step10_txt = '----step10: 线程7执行清理操作，expect: 清理成功----'
        self.log.info(step10_txt)
        sql_cmd7 = f'clean connection to all force to user {self.u_name3};'
        excute_cmd7 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session7 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd7, excute_cmd7, self.db_name))
        session7.setDaemon(True)
        session7.start()

        step11_txt = '----step11: 线程8执行清理操作，expect: 清理成功----'
        self.log.info(step11_txt)
        sql_cmd8 = f'clean connection to all force to user {self.u_name4};'
        excute_cmd8 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session8 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd8, excute_cmd8, self.db_name))
        session8.setDaemon(True)
        session8.start()

        self.log.info("----session1执行结果----")
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.err_flag, session1_result,
                      '执行失败:' + step4_txt)

        self.log.info("----session2执行结果----")
        session2.join()
        session2_result = session2.get_result()
        self.log.info(session2_result)
        self.assertIn(self.err_flag, session2_result,
                      '执行失败:' + step5_txt)

        self.log.info("----session3执行结果----")
        session3.join()
        session3_result = session3.get_result()
        self.log.info(session3_result)
        self.assertIn(self.err_flag, session3_result,
                      '执行失败:' + step6_txt)

        self.log.info("----session4执行结果----")
        session4.join()
        session4_result = session4.get_result()
        self.log.info(session4_result)
        self.assertIn(self.err_flag, session4_result,
                      '执行失败:' + step7_txt)

        self.log.info("----session5执行结果----")
        session5.join()
        session5_result = session5.get_result()
        self.log.info(session5_result)
        self.assertIn(f'non-thread pool mode not completed.',
                      session5_result, '执行失败:' + step8_txt)

        self.log.info("----session6执行结果----")
        session6.join()
        session6_result = session6.get_result()
        self.log.info(session6_result)
        self.assertIn(self.flag, session6_result,
                      '执行失败:' + step9_txt)

        self.log.info("----session7执行结果----")
        session7.join()
        session7_result = session7.get_result()
        self.log.info(session7_result)
        self.assertIn(self.flag, session7_result,
                      '执行失败:' + step10_txt)

        self.log.info("----session8执行结果----")
        session8.join()
        session8_result = session8.get_result()
        self.log.info(session8_result)
        self.assertIn(self.flag, session8_result,
                      '执行失败:' + step11_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step12: 清理环境----'
        self.log.info(step_txt)
        self.log.info('删除数据库和用户')
        drop_sql = f'drop database if exists {self.db_name};' \
                   f'drop user if exists {self.u_name1} cascade;' \
                   f'drop user if exists {self.u_name2} cascade;' \
                   f'drop user if exists {self.u_name3} cascade;' \
                   f'drop user if exists {self.u_name4} cascade;'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.log.info('恢复参数默认值')
        mod_msg1 = \
            self.pri_sh.execute_gsguc('set',
                                      self.constant.GSGUC_SUCCESS_MSG,
                                      f'enableSeparationOfDuty='
                                      f'{self.default_value}')
        self.log.info(mod_msg1)
        restart_msg = self.pri_sh.restart_db_cluster()
        self.log.info(restart_msg)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue(
            drop_result.count(self.constant.DROP_DATABASE_SUCCESS) == 1 and
            drop_result.count(self.constant.DROP_ROLE_SUCCESS_MSG) == 4,
            '执行失败:' + step_txt)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + step_txt)
        self.assertTrue(mod_msg1, '执行失败:' + step_txt)

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
